﻿/*----------------------------------------------------------------
// Copyright (C) 2017 佰林格都信息科技有限公司 版权所有。 
//
// 文件名：DMemo
// 文件功能描述：记事 备忘录 提供处理各种管理[TB_Memo]表的数据访问方法
//
// 创建标识：jinchuanliang 2018-07-14
//
// 修改标识：
// 修改描述：
//----------------------------------------------------------------*/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq; 
using FamilyTree.Entity;
using System.Text;

namespace FamilyTree.DAL
{
    /// <summary>
    /// 提供处理各种管理记事 备忘录  [TB_Memo]表的数据访问方法
    /// </summary>
	/// <author>jinchuanliang 2018-07-14</author>
    public class DMemo
    {
        /*

        /// <summary>
        /// CRUD实体操作对象
        /// </summary>
        internal readonly static EntityCRUD<MemoEntity> Crud = new EntityCRUD<MemoEntity>();

        /// <summary>
        /// 是否已存在此对应数据
        /// </summary>
        /// <param name="strVal"></param> 
        /// <returns></returns>
        public bool IsExists(string strVal)
        {
            string sql = "select MemoId from TB_Memo with(nolock) where **=@strVal ";
            List<DbParameter> plist = new List<DbParameter>();
            plist.Add(new SqlParameter("@strVal", strVal));
            object obj = SqlHelper.ExecuteValue(sql, CommandType.Text, plist);

            if (obj != null && Convert.ToInt32(obj) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        /// <summary>
        /// 添加tb_memo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public int Insert(MemoEntity model)
        {

            string sql = @"
            INSERT INTO TB_Memo(MemberId,EventTime,Brief,RecorderId,RecordingTime,OccurrenceTime,PicUrl,IsDelete)
            VALUES (@memberid,@eventtime,@brief,@recorderid,@recordingtime,@occurrencetime,@picurl,@isdelete)
            select @@identity
            ";
            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@memoid", model.MemoId));
            p.Add(new SqlParameter("@memberid", model.MemberId));
            p.Add(new SqlParameter("@eventtime", model.EventTime));
            p.Add(new SqlParameter("@brief", model.Brief));
            p.Add(new SqlParameter("@recorderid", model.RecorderId));
            p.Add(new SqlParameter("@recordingtime", model.RecordingTime));
            p.Add(new SqlParameter("@occurrencetime", model.OccurrenceTime));
            p.Add(new SqlParameter("@picurl", model.PicUrl));
            p.Add(new SqlParameter("@isdelete", model.IsDelete));

            object i = SqlHelper.ExecuteValue(sql, CommandType.Text, p);
            return Convert.ToInt32(i);
        }
         

        /// <summary>
        /// 更新表信息tb_memo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(MemoEntity model)
        {
            string sql = @"
            UPDATE TB_Memo SET MemberId=@memberid,EventTime=@eventtime,Brief=@brief,RecorderId=@recorderid,RecordingTime=@recordingtime,OccurrenceTime=@occurrencetime,PicUrl=@picurl,IsDelete=@isdelete            
             where MemoId=@memoid ";
            List<SqlParameter> p = new List<SqlParameter>();
            p.Add(new SqlParameter("@memoid", model.MemoId));
            p.Add(new SqlParameter("@memberid", model.MemberId));
            p.Add(new SqlParameter("@eventtime", model.EventTime));
            p.Add(new SqlParameter("@brief", model.Brief));
            p.Add(new SqlParameter("@recorderid", model.RecorderId));
            p.Add(new SqlParameter("@recordingtime", model.RecordingTime));
            p.Add(new SqlParameter("@occurrencetime", model.OccurrenceTime));
            p.Add(new SqlParameter("@picurl", model.PicUrl));
            p.Add(new SqlParameter("@isdelete", model.IsDelete));

            return SqlHelper.ExecuteSql(sql, p) > 0;

        }

        /// <summary>
        /// 更新表信息tb_memo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(string fileVal, string file, string strWhere)
        {
            string sql = @"
            UPDATE TB_Memo SET " + file + " = " + fileVal + " where " + strWhere;

            return SqlHelper.ExecuteSql(sql, null) > 0;
        }

        /// <summary>
        /// 标记删除tb_memo数据
        /// </summary>
        /// <param name="memoId"></param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool SignDelete(int memoId)
        {
            string sql = "update TB_Memo set isdelete=1  where MemoId=@memoId  ";

            List<SqlParameter> plist = new List<SqlParameter>();
            plist.Add(new SqlParameter("@memoId", memoId)); 

            int i = SqlHelper.ExecuteSql(sql, plist);

            return i > 0 ? true : false;
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public List<MemoEntity> GetModel(int MemoId)
        {
            const string sql = @"
            SELECT A.MemoId,A.MemberId,A.EventTime,A.Brief,A.RecorderId,A.RecordingTime,A.OccurrenceTime,A.PicUrl,A.IsDelete 
            FROM TB_Memo A with(nolock) where MemoId=@MemoId ";

            List<DbParameter> p = new List<DbParameter>();

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);
            p.Add(new SqlParameter("@MemoId", MemoId));

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemoEntity>(dt);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public List<MemoEntity> GetModels(int memberId)
        {
            const string sql = @"
            SELECT A.MemoId,A.MemberId,A.EventTime,A.Brief,A.RecorderId,A.RecordingTime,A.OccurrenceTime,A.PicUrl,A.IsDelete 
            FROM TB_Memo A with(nolock) where memberId=@memberId and isdelete=0  ";

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@memberId", memberId));

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemoEntity>(dt);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public List<MemoEntity> GetModels(int memberId,int pageIndex, int pageSize)
        {
            StringBuilder sb = new StringBuilder(@"
            SELECT top (@pageSize) A.MemoId,A.MemberId,A.EventTime,A.Brief,A.RecorderId,A.RecordingTime,A.OccurrenceTime,A.PicUrl,A.IsDelete 
            FROM TB_Memo A with(nolock) where memberId=@memberId and isdelete=0 ");
            if (pageIndex > 1)
            {
                sb.Append(@" and A.MemoId <(select min(MemoId) as mid from (select top ((@pageIndex-1)*@pageSize) MemoId from TB_Memo where memberId=@memberId and isdelete=0  order by MemoId desc) t)");
            }
            sb.Append(" order by A.MemoId desc ");

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@memberId", memberId));
            p.Add(new SqlParameter("@pageIndex", pageIndex));
            p.Add(new SqlParameter("@pageSize", pageSize));

            DataTable dt = SqlHelper.ExecuteDt(sb.ToString(), CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemoEntity>(dt);
            }
            else
            {
                return null;
            }
        }

        */
    }
}

